package com.student.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.student.DBConn;
import com.student.entities.GoodsEntity;

public class GoodsService {

	public static List<GoodsEntity> getGoodsEntity(int n) throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods ,shop_seller,shop_goodsclass  where  shop_goods.goods_sellerId = shop_seller.seller_id "
				+ " and shop_goods.goods_classId = shop_goodsclass.class_id and goods_state=1 order by goods_saleCount desc limit "
				+ n;

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next() && n > 0) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setClassName(rs.getString("class_name"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));

			goodsList.add(goods);

			n--;
		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	public static List<GoodsEntity> getSellerGoodsEntity(String id)
			throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods,shop_seller,shop_goodsclass where goods_classId = class_id and  shop_goods.goods_sellerId = "
				+ id
				+ " and shop_goods.goods_sellerId = shop_seller.seller_id and goods_state=1";
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goods.setClassName(rs.getString("class_name"));

			goodsList.add(goods);
		}
		stmt.close();
		conn.close();
		return goodsList;
	}

	public static GoodsEntity getPointGoods(int id) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods , shop_goodsclass where goods_classId = class_id and goods_id = "
				+ id;
		GoodsEntity goods = new GoodsEntity();
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goods.setClassName(rs.getString("class_name"));
		}
		rs.close();
		stmt.close();
		conn.close();
		return goods;
	}

	// 针对搜索
	public static List<GoodsEntity> searchResult(String classKey,
			String priceKey, String key) throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = " select shop_goods.*,seller_name,class_name from shop_goods LEFT JOIN shop_seller  on seller_id = goods_sellerId , shop_goodsclass"
				+ " where  goods_classId = class_id and ";
		if ("all".equals(classKey)) {
			sql += " ( goods_name like '%" + key + "%' or class_name like '%"
					+ key + "%' or seller_name like '%" + key + "%' )";
		} else {
			sql += classKey + " like '%" + key + "%'";
		}
		sql += " and goods_price " + priceKey + " group by goods_id;";

		System.out.println(sql);

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goods.setClassName(rs.getString("class_name"));
			goodsList.add(goods);
		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	public static List<GoodsEntity> getHotGoods() throws SQLException {
		return getGoodsEntity(10);
	}

	public static List<GoodsEntity> getAllGoods() throws SQLException {
		return getGoodsEntity(Integer.MAX_VALUE);
	}

	// 随机获得商品
	public static List<GoodsEntity> getRandGoods() throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods ,shop_seller,shop_goodsclass  where  shop_goods.goods_sellerId = shop_seller.seller_id "
				+ " and shop_goods.goods_classId = shop_goodsclass.class_id and goods_state = 1  order by rand() desc limit 10";

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setClassName(rs.getString("class_name"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));

			goodsList.add(goods);

		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	// 随机获得商品
	public static List<GoodsEntity> getRecentGoods() throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods ,shop_seller,shop_goodsclass  where  shop_goods.goods_sellerId = shop_seller.seller_id "
				+ " and shop_goods.goods_classId = shop_goodsclass.class_id  and goods_state = 1 order by goods_onSaleTime desc limit 10";

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setClassName(rs.getString("class_name"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));

			goodsList.add(goods);

		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	public static void main(String args[]) throws SQLException {
		System.out.println(new Date());
		System.out.println(getAllGoods());
		System.out.println(getHotGoods());
		System.out.println(getPointGoods(4));
		System.out.println(getSellerGoodsEntity(2 + ""));
		System.out.println(getRandGoods());
		System.out.println(getRecentGoods());
		System.out.println(new Date());

	}
}
